package projects

import (
	"github.com/beego/beego/v2/client/orm"
	"xpms/cores"
	"xpms/cores/utils"
)

type AnalysisAllTest struct {
	Projectid   int64 `json:"Projectid,string"`
	Mouleid     int64 `json:"Mouleid,string"`
	Projectname string
	Modulename  string
	Type        int
	Typename    string
	Status10,
	Status11,
	Status12,
	Status20,
	Status21,
	Status22,
	Status30,
	Status31,
	Status32,
	Status40,
	Status41,
	Status42,
	Status50,
	Status51,
	Status70,
	Status80,
	Status81,
	Status82,
	Status90,
	Total int
}

func AnlysisTestByProject(condArr map[string]interface{}) (ops []AnalysisAllTest) {
	o := orm.NewOrm()
	var analysisAllTest []AnalysisAllTest
	o.Raw(getAnlysisTestByProjectSql(condArr)).QueryRows(&analysisAllTest)
	return analysisAllTest
}

/**
  项目维度的统计
*/
func getAnlysisTestByProjectSql(condArr map[string]interface{}) (string, []interface{}) {
	sql := `
SELECT  projectid projectid,
 mouleid mouleid,
  projectname projectname,
  modulename modulename,
  type type,
  typename typename, 
  status10,
  status11,
  status12,
  status20,
  status21,
  status22,
  status30,
  status31,
  status32,
  status40,
  status41,
  status42,
  status50,
  status51,
  status70,
  status80,
  status81,
  status82,
  status90,
  status10+status11+status12+status20+status21+status22+status30+status31+status32+status40+status41+status42+status50+status51+status70+status80+status81+status82+status90 total
    FROM (SELECT
  projectid projectid,
  c.projectmoduleid mouleid,
  projectname projectname,
  modulename modulename,
  type type,
  typename typename,
  SUM(CASE c.status WHEN 10 THEN 1 ELSE 0 END) status10, -- 待确认
  SUM(CASE c.status WHEN 11 THEN 1 ELSE 0 END) status11, -- 确认中
  SUM(CASE c.status WHEN 12 THEN 1 ELSE 0 END) status12,-- 确认完成
  SUM(CASE c.status WHEN 20 THEN 1 ELSE 0 END) status20,-- 对应中
  SUM(CASE c.status WHEN 21 THEN 1 ELSE 0 END) status21,-- 待开始
  SUM(CASE c.status WHEN 22 THEN 1 ELSE 0 END) status22,-- 连接测试完成
  SUM(CASE c.status WHEN 30 THEN 1 ELSE 0 END) status30,-- 系统测试OK
  SUM(CASE c.status WHEN 31 THEN 1 ELSE 0 END) status31,-- 待系统测试
  SUM(CASE c.status WHEN 32 THEN 1 ELSE 0 END) status32,-- 系统测试NG
  SUM(CASE c.status WHEN 40 THEN 1 ELSE 0 END) status40,-- 业务验收OK
  SUM(CASE c.status WHEN 41 THEN 1 ELSE 0 END) status41,-- 待业务验收
  SUM(CASE c.status WHEN 42 THEN 1 ELSE 0 END) status42,-- 业务验收NG
  SUM(CASE c.status WHEN 50 THEN 1 ELSE 0 END) status50,-- 取消
  SUM(CASE c.status WHEN 51 THEN 1 ELSE 0 END) status51,-- 待取消
  SUM(CASE c.status WHEN 70 THEN 1 ELSE 0 END) status70,-- 保留
  SUM(CASE c.status WHEN 80 THEN 1 ELSE 0 END) status80,-- 待客户验收
  SUM(CASE c.status WHEN 81 THEN 1 ELSE 0 END) status81,-- 客户验收OK
  SUM(CASE c.status WHEN 82 THEN 1 ELSE 0 END) status82,-- 客户验收NG
  SUM(CASE c.status WHEN 90 THEN 1 ELSE 0 END) status90 -- 已发布生产
FROM (SELECT
    ppt.*,
    CASE ppt.type WHEN 1 THEN '程序bug' WHEN 2 THEN '需求问题' WHEN 3 THEN '需求变更' WHEN 4 THEN '无需对应' WHEN 5 THEN '设计问题' WHEN 6 THEN '数据问题' WHEN 7 THEN '版本问题' WHEN 8 THEN '环境问题' WHEN 9 THEN '重复' WHEN 10 THEN '用户体验' END typeName,
    pp.name AS projectName,
    ppm.name AS moduleName
  FROM pms_projects_test ppt
    INNER JOIN pms_projects pp
      ON pp.projectid = ppt.projectid
    INNER JOIN pms_projects_module ppm
      ON ppm.moduleid = ppt.projectmoduleid where 1=1 `
	param := make([]interface{}, 0, 0)
	if !utils.IsEmpty(condArr["projectid"]) {
		sql = sql + ` and  ppt.projectid=?`
		param = append(param, condArr["projectid"])
	}
	if !utils.IsEmpty(condArr["projectmoduleid"]) {
		sql = sql + ` and  ppt.projectmoduleid in(`
		sql1, value := cores.SqlInParam(condArr["projectmoduleid"])
		sql = sql + sql1 + `)`
		param = append(param, value...)
	}
	if !utils.IsEmpty(condArr["creatId"]) {
		sql = sql + ` and  ppt.userid in(`
		sql1, value := cores.SqlInParam(condArr["creatId"])
		sql = sql + sql1 + `)`
		param = append(param, value...)
	}
	if !utils.IsEmpty(condArr["acceptid"]) {
		sql = sql + ` and  ppt.acceptid in(`
		sql1, value := cores.SqlInParam(condArr["acceptid"])
		sql = sql + sql1 + `)`
		param = append(param, value...)
	}
	if !utils.IsEmpty(condArr["level"]) {
		sql = sql + ` and  ppt.level in(`
		sql1, value := cores.SqlInParam(condArr["level"])
		sql = sql + sql1 + `)`
		param = append(param, value...)
	}
	if !utils.IsEmpty(condArr["env"]) {
		sql = sql + ` and  ppt.env in(`
		sql1, value := cores.SqlInParam(condArr["env"])
		sql = sql + sql1 + `)`
		param = append(param, value...)
	}
	if !utils.IsEmpty(condArr["type"]) {
		sql = sql + ` and  ppt.type in(`
		sql1, value := cores.SqlInParam(condArr["type"])
		sql = sql + sql1 + `)`
		param = append(param, value...)
	}
	if !utils.IsEmpty(condArr["createStarted"]) {
		sql = sql + ` and  ppt.created >=?`
		param = append(param, condArr["createStarted"])
	}
	if !utils.IsEmpty(condArr["createEnd"]) {
		sql = sql + ` and  ppt.created <=?`
		param = append(param, condArr["createEnd"])
	}
	sql = sql + `) c GROUP BY c.projectid, c.projectmoduleid,c.type)n`
	return sql, param
}
func AnlysisTestAllByProject(condArr map[string]interface{}) (ops []AnalysisAllTest) {
	o := orm.NewOrm()
	var analysisAllTest []AnalysisAllTest
	o.Raw(getAnlysisTestByProjectAllSql(condArr)).QueryRows(&analysisAllTest)
	return analysisAllTest
}

/**
  项目整体维度的统计
*/
func getAnlysisTestByProjectAllSql(condArr map[string]interface{}) (string, []interface{}) {
	sql := `
SELECT  projectid projectid,
  projectname projectname,
  type type,
  typename typename, 
  status10,
  status11,
  status12,
  status20,
  status21,
  status22,
  status30,
  status31,
  status32,
  status40,
  status41,
  status42,
  status50,
  status51,
  status70,
  status80,
  status81,
  status82,
  status90,
  status10+status11+status12+status20+status21+status22+status30+status31+status32+status40+status41+status42+status50+status51+status70+status80+status81+status82+status90 total
    FROM (SELECT
  projectid projectid,
  c.projectmoduleid mouleid,
  projectname projectname,
  modulename modulename,
  type type,
  typename typename,
  SUM(CASE c.status WHEN 10 THEN 1 ELSE 0 END) status10, -- 待确认
  SUM(CASE c.status WHEN 11 THEN 1 ELSE 0 END) status11, -- 确认中
  SUM(CASE c.status WHEN 12 THEN 1 ELSE 0 END) status12,-- 确认完成
  SUM(CASE c.status WHEN 20 THEN 1 ELSE 0 END) status20,-- 对应中
  SUM(CASE c.status WHEN 21 THEN 1 ELSE 0 END) status21,-- 待开始
  SUM(CASE c.status WHEN 22 THEN 1 ELSE 0 END) status22,-- 连接测试完成
  SUM(CASE c.status WHEN 30 THEN 1 ELSE 0 END) status30,-- 系统测试OK
  SUM(CASE c.status WHEN 31 THEN 1 ELSE 0 END) status31,-- 待系统测试
  SUM(CASE c.status WHEN 32 THEN 1 ELSE 0 END) status32,-- 系统测试NG
  SUM(CASE c.status WHEN 40 THEN 1 ELSE 0 END) status40,-- 业务验收OK
  SUM(CASE c.status WHEN 41 THEN 1 ELSE 0 END) status41,-- 待业务验收
  SUM(CASE c.status WHEN 42 THEN 1 ELSE 0 END) status42,-- 业务验收NG
  SUM(CASE c.status WHEN 50 THEN 1 ELSE 0 END) status50,-- 取消
  SUM(CASE c.status WHEN 51 THEN 1 ELSE 0 END) status51,-- 待取消
  SUM(CASE c.status WHEN 70 THEN 1 ELSE 0 END) status70,-- 保留
  SUM(CASE c.status WHEN 80 THEN 1 ELSE 0 END) status80,-- 待客户验收
  SUM(CASE c.status WHEN 81 THEN 1 ELSE 0 END) status81,-- 客户验收OK
  SUM(CASE c.status WHEN 82 THEN 1 ELSE 0 END) status82,-- 客户验收NG
  SUM(CASE c.status WHEN 90 THEN 1 ELSE 0 END) status90 -- 已发布生产
FROM (SELECT
    ppt.*,
    CASE ppt.type WHEN 1 THEN '程序bug' WHEN 2 THEN '需求问题' WHEN 3 THEN '需求变更' WHEN 4 THEN '无需对应' WHEN 5 THEN '设计问题' WHEN 6 THEN '数据问题' WHEN 7 THEN '版本问题' WHEN 8 THEN '环境问题' WHEN 9 THEN '重复' WHEN 10 THEN '用户体验' END typeName,
    pp.name AS projectName,
    ppm.name AS moduleName
  FROM pms_projects_test ppt
    INNER JOIN pms_projects pp
      ON pp.projectid = ppt.projectid
    INNER JOIN pms_projects_module ppm
      ON ppm.moduleid = ppt.projectmoduleid where 1=1 `
	param := make([]interface{}, 0, 0)
	if !utils.IsEmpty(condArr["projectid"]) {
		sql = sql + ` and  ppt.projectid in(`
		sql1, value := cores.SqlInParam(condArr["projectid"])
		sql = sql + sql1 + `)`
		param = append(param, value...)
	}
	if !utils.IsEmpty(condArr["projectmoduleid"]) {
		sql = sql + ` and  ppt.projectmoduleid in(`
		sql1, value := cores.SqlInParam(condArr["projectmoduleid"])
		sql = sql + sql1 + `)`
		param = append(param, value...)
	}
	if !utils.IsEmpty(condArr["creatId"]) {
		sql = sql + ` and  ppt.userid in(`
		sql1, value := cores.SqlInParam(condArr["creatId"])
		sql = sql + sql1 + `)`
		param = append(param, value...)
	}
	if !utils.IsEmpty(condArr["acceptid"]) {
		sql = sql + ` and  ppt.acceptid in(`
		sql1, value := cores.SqlInParam(condArr["acceptid"])
		sql = sql + sql1 + `)`
		param = append(param, value...)
	}
	if !utils.IsEmpty(condArr["level"]) {
		sql = sql + ` and  ppt.level in(`
		sql1, value := cores.SqlInParam(condArr["level"])
		sql = sql + sql1 + `)`
		param = append(param, value...)
	}
	if !utils.IsEmpty(condArr["env"]) {
		sql = sql + ` and  ppt.env in(`
		sql1, value := cores.SqlInParam(condArr["env"])
		sql = sql + sql1 + `)`
		param = append(param, value...)
	}
	if !utils.IsEmpty(condArr["type"]) {
		sql = sql + ` and  ppt.type in(`
		sql1, value := cores.SqlInParam(condArr["type"])
		sql = sql + sql1 + `)`
		param = append(param, value...)
	}
	if !utils.IsEmpty(condArr["createStarted"]) {
		sql = sql + ` and  ppt.created >=?`
		param = append(param, condArr["createStarted"])
	}
	if !utils.IsEmpty(condArr["createEnd"]) {
		sql = sql + ` and  ppt.created <=?`
		param = append(param, condArr["createEnd"])
	}
	sql = sql + `) c GROUP BY c.projectid,c.type)n`
	return sql, param
}
